﻿-- 客户列表
drop table custom_list;
create table custom_list(
  custom_id number(20) not null primary key ,
  custom_name varchar2(255) not null,
  follow_up_status number(1) not null,
  channel varchar(255) default ' ' ,
  stage number(1) default 0 ,
  address varchar2(255) default ' ' ,
  follow_up_person varchar(32) default 'admin' ,
  create_by varchar2(32) default 'admin' ,
  create_date date ,
  create_time date  ,
  update_by varchar2(32) default ' ' ,
  update_time varchar2(32) default ' ' ,
  is_deleted number(1) default 0 
);

comment on table custom_list is '客户列表';
comment on column custom_list.custom_id is '客户列表主题ID';
comment on column custom_list.custom_name is '企业名称';
comment on column custom_list.follow_up_status is '跟进状态 1已跟进 2待跟进 3未跟进';
comment on column custom_list.channel is '渠道： 0地推 1 官网 2客户介绍  3广告 4搜索引擎 5熟人介绍 6社交推广';
comment on column custom_list.stage is '阶段, 0:未成交客户 1:成交客户 2:多次成交用户 3:公海用户';
comment on column custom_list.address is '地址';
comment on column custom_list.follow_up_person is '跟进人';
comment on column custom_list.create_by is '创建人';
comment on column custom_list.create_date is '创建日期';
comment on column custom_list.create_time is '创建时间';
comment on column custom_list.update_by is '更新人';
comment on column custom_list.update_time is '更新时间';
comment on column custom_list.is_deleted is '删除状态 0 未删除 1 已删除';

insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'++0企业',1,0,'广东广州天河',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'东莞企业',1,1,'123',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'张三公司',1,1,'广东省',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'3344',2,6,'广州',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'3344',2,0,'广州',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'3344',1,0,'广州白云',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'通讯技术有限公司',1,1,'广东东莞',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'合堂有限公司',1,2,'广东省湛江市',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'rabbit',2,6,'广东省潮州市',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'公主之家',2,1,'上海',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'参半有限公司',1,4,'广州市白云区',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'魔石企业',1,6,'福建厦门',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'Samsung',3,1,'韩国',sysdate,sysdate);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_date,create_time)values(seq_custom_list_id.nextval,'FILA',3,0,'广东广州',sysdate,sysdate);

drop sequence seq_custom_list_id;
create sequence seq_custom_list_id;
commit;
select * from CUSTOM_LIST 

-- 联系人列表
drop table contacts_list;
create table contacts_list(
  contacts_id number(10) not null primary key ,
  contacts_name varchar2(32) not null , 
  phone char(11) not null,
  job varchar(32) default '',
  sex number(2) not null ,
  duties varchar2(32) default ' ',
  create_by varchar2(32) default 'admin',
  create_time date null,
  update_by varchar2(32) default ' ',
  update_time date default sysdate, 
  is_deleted number(2) default 0
);
comment on table contacts_list is '联系人列表';
comment on column contacts_list.contacts_id is '联系人列表主键id';
comment on column contacts_list.contacts_name is '联系人姓名';
comment on column contacts_list.phone is '手机号';
comment on column contacts_list.job is '所属企业';
comment on column contacts_list.sex is '性别: 0 男  1 女';
comment on column contacts_list.duties is '职务';
comment on column contacts_list.create_by is '创建人';
comment on column contacts_list.create_time is '创建时间';
comment on column contacts_list.update_by is '更新人';
comment on column contacts_list.update_by is '更新时间';
comment on column contacts_list.is_deleted is '可删除状态,0 未删除 1 已删除'; 

insert into contacts_list(contacts_id,contacts_name,phone,job,sex,duties) values(seq_contacts_id.nextval,'+0','13559687455','++0企业',0,'外交官');
insert into contacts_list(contacts_id,contacts_name,phone,job,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'张三','19867935678','张三公司',0,'副总','1',sysdate);
insert into contacts_list(contacts_id,contacts_name,phone,job,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'呜啦啦','15846321841','3344',0,'经理','1',sysdate);
insert into contacts_list(contacts_id,contacts_name,phone,job,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'张三','12312312312','东莞企业',0,'boss','1',sysdate);

drop sequence seq_contacts_id;
create sequence seq_contacts_id;

commit;
select * from contacts_list

-- 客户_联系人列表
drop table custom_contacts_list;
create table custom_contacts_list(
       id number(2) not null primary key,
       custom_id number(1) not null,
       contacts_id number(1) not null
);
comment on table custom_contacts_list is '客户_联系人列表';
comment on column custom_contacts_list.id is '客户_联系人列表主键ID';
comment on column custom_contacts_list.custom_id is '客户ID';
comment on column custom_contacts_list.contacts_id is '联系人ID';

insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,2,2);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,3,5);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,4,3);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,5,4);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,6,2);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,7,3);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,8,2);
insert into custom_contacts_list(id,custom_id,contacts_id) values(seq_cu_con_list.nextval,9,5);



drop sequence seq_cu_con_list;
create sequence seq_cu_con_list;

commit;

select * from custom_contacts_list

-- 测试数据
declare
   v_custom_id integer;
   v_contacts_id integer;
begin
  for i in 1 .. 10 loop
    select trunc(dbms_random.value(2,6)) into v_custom_id from dual;
    select trunc(dbms_random.value(2,6)) into v_contacts_id from dual;
    insert into custom_contacts_list(id,custom_id,contacts_id)
    values(seq_cu_con_list.nextval,v_custom_id,v_contacts_id);
  end loop;
  commit;
end;

select dbms_random.value(2,6) from dual; 

select * from CUSTOM_LIST ;
select * from contacts_list;
select * from custom_contacts_list;


-- 多表联查
select rownum,info.* from(
select distinct cus.custom_name 企业名称,con.contacts_name 联系人名称,
       decode(cus.follow_up_status,0,' ',1,'已跟进'，2，'待跟进'，3，'未跟进') 跟进状态,
       cus.channel 渠道,cus.address 地址,to_char(cus.create_date,'YYYY-MM-DD') 创建日期,
       to_char(cus.create_time,'hh24:mi') 创建时间 from custom_list cus
left join custom_contacts_list ccl on ccl.custom_id = cus.custom_id
left join contacts_list con on con.contacts_id = ccl.contacts_id
)info;





declare 
     type rec_custom is record(
       v_id  number(10),
       v_custom_name custom_list.custom_name%type,
       v_contacts_name contacts_list.contacts_name%type,
       v_follow_status custom_list.follow_up_status%type,
       v_follow_str varchar2(32),
       v_channel custom_list.channel%type,
       v_address custom_list.address%type,
       v_create_date custom_list.create_date%type,
       v_create_time custom_list.create_time%type
       );
       recoed_cust rec_custom;
       
      cursor cursor_custom is 
              select rownum,info.* from(
                     select distinct cus.custom_name 企业名称,con.contacts_name 联系人名称,
                     cus.follow_up_status 跟进状态 ,decode(cus.follow_up_status,0,' ',1,'已跟进'，2，'待跟进'，3，'未跟进') 跟进状态中文,
                      cus.channel 渠道,cus.address 地址,to_char(cus.create_date,'YYYY-MM-DD') 创建日期,
                      to_char(cus.create_time,'hh24:mi') 创建时间 
                      from custom_list cus
                     left join custom_contacts_list ccl on ccl.custom_id = cus.custom_id
                     left join contacts_list con on con.contacts_id = ccl.contacts_id
               )info;
begin
  open cursor_custom;
  fetch cursor_custom into recoed_cust.v_id,recoed_cust.v_custom_name,recoed_cust.v_contacts_name,recoed_cust.v_follow_status,recoed_cust.v_follow_str, recoed_cust.v_channel,recoed_cust.v_address,recoed_cust.v_create_date,recoed_cust.v_create_time;
  while cursor_custom%found loop
    dbms_output.put_line(recoed_cust.v_id||recoed_cust.v_custom_name||recoed_cust.v_contacts_name ||recoed_cust.v_follow_status|| recoed_cust.v_channel|| recoed_cust.v_address||to_char(recoed_cust.v_create_date,'yyyy-mm-dd hh24:mi')||to_char(recoed_cust.v_create_time,'yyyy-mm-dd hh24:mi'));
  fetch cursor_custom into recoed_cust.v_id,recoed_cust.v_custom_name,recoed_cust.v_contacts_name,recoed_cust.v_follow_status,recoed_cust.v_follow_str, recoed_cust.v_channel,recoed_cust.v_address,recoed_cust.v_create_date,recoed_cust.v_create_time;
  end loop;
  close cursor_custom;
end;



